# coding=utf-8
import sqlite3

class sqlHandler:
    
    def __init__(self):
        self.dbPath="qcm.db" # à changer
        self.connection = sqlite3.connect(self.dbPath)
        self.cursor = self.connection.cursor()
        
        
    def createDB(self):
        #tuple contenant les différents statements à éxecuter pour créer la BDD
        creation = ('''CREATE TABLE year (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    name text NOT NULL)''',
                    '''CREATE TABLE serie (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    name text NOT NULL);''',
                    '''CREATE TABLE subserie (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                    name text NOT NULL);''',
                    '''CREATE TABLE question (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                        question TEXT NOT NULL, 
                        answer INTEGER NOT NULL, 
                        comment TEXT NOT NULL);''',
                    '''CREATE TABLE qcm (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                        idYear INTEGER NOT NULL, 
                        idSerie INTEGER NOT NULL, 
                        idSubSerie INTEGER NOT NULL, 
                        idQuestion INTEGER NOT NULL);''',)
        #on execute chacun des statement
        for statement in creation :
            self.cursor.execute(statement)
        #on sauvegarde
        self.connection.commit()
        
    def newYear(self, name):
        y = (name,)
        self.cursor.execute("INSERT INTO year (name) VALUES (?)", y)
        self.connection.commit()
    def newSerie(self, name):
        s = (name,)
        self.cursor.execute("INSERT INTO serie (name) VALUES (?)", s)
        self.connection.commit()
    def newSubSerie(self, name):
        ss = (name,)
        self.cursor.execute("INSERT INTO subserie (name) VALUES (?)", ss)
        self.connection.commit()
    def newQuestion(self, question, answer, comment):
        question = (question, answer, comment,)
        self.cursor.execute("INSERT INTO question (question, answer, comment) VALUES (?,?,?)",question)
        self.connection.commit()
        
